/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import bean.Encuesta;
import db.ConexionDB;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.naming.NamingException;

/**
 *
 * @author cesardoncel
 */
public class EncuestaDAO {
    public String insertarEncuesta(Encuesta enc) throws NamingException, SQLException{
        Connection conn = null;
        try {
        //System.out.println(enc.getNombre() + " - " + enc.getCargo()  + " - " + enc.getP1()  + " - " + enc.getP2()  + " - " + enc.getP3());
        
        conn = ConexionDB.getInstance().getConnection();
        if (conn == null) {
            throw new SQLException("Error establishing connection!");
        }
        Date d = new Date();
        String query = "insert into enc_fidu_jun2013 (idFiduprevisora, fecha, nombre, cargo, p1, p2, p3, p4, "
                + "p5, p7, p8, p9a, p9b, p9c, p9d, p9e, p9f, p9g, p9h, p9i, p10a, p10b, p10c, "
                + "p10d, p11a, p11b, p11c, p11d, p11e, p11f, p11g, p11h, p11i, p11j, p12a, p12b, "
                + "p12c, p12d, p13a,  p13b, p13c, p13d, p13e, p14a, p14b, p14c, p14d, p14e, p14f, "
                + "p14g, p14h, p15a, p15b, p15c, p16a, p16b, p16c, p16d, p17a, p17b, p17c, p17d, "
                + "p17e, p17f, p17g, p18, p19, cedula, tiempo, area"                
                + ") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
                + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
                + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";        
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setInt(1, mayorId());
        java.sql.Date dta = new java.sql.Date(enc.getFecha().getTime());
        statement.setDate(2, dta);
        statement.setString(3, enc.getNombre());
        statement.setString(4, enc.getCargo());
        statement.setString(5, enc.getP1());
        statement.setString(6, enc.getP2());
        statement.setString(7, enc.getP3());
        statement.setString(8, enc.getP4());
        statement.setString(9, enc.getP5());
        statement.setString(10, enc.getP7());
        statement.setString(11, enc.getP8());
        statement.setString(12, enc.getP9a());
        statement.setString(13, enc.getP9b());
        statement.setString(14, enc.getP9c());
        statement.setString(15, enc.getP9d());
        statement.setString(16, enc.getP9e());
        statement.setString(17, enc.getP9f());
        statement.setString(18, enc.getP9g());
        statement.setString(19, enc.getP9h());
        statement.setString(20, enc.getP9i());
        statement.setString(21, enc.getP10a());
        statement.setString(22, enc.getP10b());
        statement.setString(23, enc.getP10c());
        statement.setString(24, enc.getP10d());
        statement.setString(25, enc.getP11a());
        statement.setString(26, enc.getP11b());
        statement.setString(27, enc.getP11c());
        statement.setString(28, enc.getP11d());
        statement.setString(29, enc.getP11e());
        statement.setString(30, enc.getP11f());
        statement.setString(31, enc.getP11g());
        statement.setString(32, enc.getP11h());
        statement.setString(33, enc.getP11i());
        statement.setString(34, enc.getP11j());
        statement.setString(35, enc.getP12a());
        statement.setString(36, enc.getP12b());
        statement.setString(37, enc.getP12c());
        statement.setString(38, enc.getP12d());
        statement.setString(39, enc.getP13a());
        statement.setString(40, enc.getP13b());
        statement.setString(41, enc.getP13c());
        statement.setString(42, enc.getP13d());
        statement.setString(43, enc.getP13e());
        statement.setString(44, enc.getP14a());
        statement.setString(45, enc.getP14b());
        statement.setString(46, enc.getP14c());
        statement.setString(47, enc.getP14d());
        statement.setString(48, enc.getP14e());
        statement.setString(49, enc.getP14f());
        statement.setString(50, enc.getP14g());
        statement.setString(51, enc.getP14h());
        statement.setString(52, enc.getP15a());
        statement.setString(53, enc.getP15b());
        statement.setString(54, enc.getP15c());
        statement.setString(55, enc.getP16a());
        statement.setString(56, enc.getP16b());
        statement.setString(57, enc.getP16c());
        statement.setString(58, enc.getP16d());
        statement.setString(59, enc.getP17a());
        statement.setString(60, enc.getP17b());
        statement.setString(61, enc.getP17c());        
        statement.setString(62, enc.getP17d());
        statement.setString(63, enc.getP17e());
        statement.setString(64, enc.getP17f());
        statement.setString(65, enc.getP17g());
        statement.setString(66, enc.getP18());
        statement.setString(67, enc.getP19());
        statement.setString(68, enc.getCedula());
        statement.setString(69, enc.getTiempo());
        statement.setString(70, enc.getArea());
        //statement.setString
        statement.executeUpdate();
        //conn.commit();
        } catch (Exception e){
            System.err.println("Problema en consulta." + e);
        }
        finally {
            try {
                conn.close();
            } catch (SQLException e) {
                    
            }
        }
        return null;
    }
    
    public int mayorId() throws NamingException, SQLException{
        int mayor = 0;
        Connection conn = null;
        try {
            conn = ConexionDB.getInstance().getConnection();
            if (conn == null) {
                throw new SQLException("Error establishing connection!");
            }
            String q = "select max(idFiduprevisora) from enc_fidu_jun2013";
            PreparedStatement ps = conn.prepareStatement(q);
            
            ResultSet rs = ps.executeQuery(q);
            if (rs.next()){
                mayor = rs.getInt("max(idFiduprevisora)");
            }
        } catch (Exception e){
            System.err.println("Problema en consulta de mayor id." + e);
         }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                    
            }
        }
        return mayor+1;
    }
    
    public List<Encuesta> findAll() throws NamingException, SQLException{
        List<Encuesta> lsEncuestados = new ArrayList<Encuesta>();
        Connection conn = null;
        try {          
            conn = ConexionDB.getInstance().getConnection();
            if (conn == null) {
                throw new SQLException("Error establishing connection!");
            }
            String q = "select * from enc_fidu_jun2013";
            PreparedStatement ps = conn.prepareStatement(q);            
            ResultSet rs = ps.executeQuery(q);
            while (rs.next()){
                Encuesta e = new Encuesta();
                e.setId(rs.getInt("idFiduprevisora"));
                e.setFecha(rs.getDate("fecha"));
                e.setNombre(rs.getString("nombre"));
                e.setCargo(rs.getString("cargo"));
                e.setP1(rs.getString("p1"));
                e.setP2(rs.getString("p2"));
                e.setP3(rs.getString("p3"));
                e.setP4(rs.getString("p4"));
                e.setP5(rs.getString("p5"));
                e.setP7(rs.getString("p7"));
                e.setP8(rs.getString("p8"));
                e.setP9a(rs.getString("p9a"));
                e.setP9b(rs.getString("p9b"));
                e.setP9c(rs.getString("p9c"));
                e.setP9d(rs.getString("p9d"));
                e.setP9e(rs.getString("p9e"));
                e.setP9f(rs.getString("p9f"));
                e.setP9g(rs.getString("p9g"));
                e.setP9h(rs.getString("p9h"));
                e.setP9i(rs.getString("p9i"));
                e.setP10a(rs.getString("p10a"));
                e.setP10b(rs.getString("p10b"));
                e.setP10c(rs.getString("p10c"));
                e.setP10d(rs.getString("p10d"));
                e.setP11a(rs.getString("p11a"));
                e.setP11b(rs.getString("p11b"));
                e.setP11c(rs.getString("p11c"));
                e.setP11d(rs.getString("p11d"));
                e.setP11e(rs.getString("p11e"));
                e.setP11f(rs.getString("p11f"));
                e.setP11g(rs.getString("p11g"));
                e.setP11h(rs.getString("p11h"));
                e.setP11i(rs.getString("p11i"));
                e.setP11j(rs.getString("p11j"));
                e.setP12a(rs.getString("p12a"));
                e.setP12b(rs.getString("p12b"));
                e.setP12c(rs.getString("p12c"));
                e.setP12d(rs.getString("p12d"));
                e.setP13a(rs.getString("p13a"));
                e.setP13b(rs.getString("p13b"));
                e.setP13c(rs.getString("p13c"));
                e.setP13d(rs.getString("p13d"));
                e.setP13e(rs.getString("p13e"));
                e.setP14a(rs.getString("p14a"));
                e.setP14b(rs.getString("p14b"));
                e.setP14c(rs.getString("p14c"));
                e.setP14d(rs.getString("p14d"));
                e.setP14e(rs.getString("p14e"));
                e.setP14f(rs.getString("p14f"));
                e.setP14g(rs.getString("p14g"));
                e.setP14h(rs.getString("p14h"));
                e.setP15a(rs.getString("p15a"));
                e.setP15b(rs.getString("p15b"));
                e.setP15c(rs.getString("p15c"));
                e.setP16a(rs.getString("p16a"));
                e.setP16b(rs.getString("p16b"));
                e.setP16c(rs.getString("p16c"));
                e.setP16d(rs.getString("p16d"));
                e.setP17a(rs.getString("p17a"));
                e.setP17b(rs.getString("p17b"));
                e.setP17c(rs.getString("p17c"));
                e.setP17d(rs.getString("p17d"));
                e.setP17e(rs.getString("p17e"));
                e.setP17f(rs.getString("p17f"));
                e.setP17g(rs.getString("p17g"));
                e.setP18(rs.getString("p18"));
                e.setP19(rs.getString("p19"));
                e.setCedula(rs.getString("cedula"));
                e.setTiempo(rs.getString("tiempo"));
                e.setArea(rs.getString("area"));
                lsEncuestados.add(e);
                System.out.println("lsEncu " + lsEncuestados.size());
            }
        } catch (Exception e){
            System.err.println("Problema en consulta de mayor id." + e);
        
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                    
            }
        }
        return lsEncuestados;
    }
}
